package edu.uic.kott.db;

import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

public class KottDBOpenHelper extends SQLiteOpenHelper {

	private static final String LOGTAG = "KOTT";

	private static final String DATABASE_NAME = "kott.db";
	private static final int DATABASE_VERSION = 22; 

	//table name, table columns and create table query for AllCourses	
	public static final String TABLE_ALL_COURSES = "all_courses";
	public static final String COURSE_ID = "course_id";
	public static final String COURSE_NAME = "course_name";
	public static final String START_DATE = "start_date";
	public static final String END_DATE = "end_date";
	public static final String CLASS_DAYS = "class_days";
	public static final String START_TIME = "start_time";
	public static final String END_TIME = "end_time";
	public static final String TWSS = "targeted_wss";
	public static final String CLASS_LOCATION = "class_location";
	public static final String INSTRUCTOR = "instructor";
	public static final String TA = "ta";

	public static final String CREATE_TABLE_ALL_COURSES = 
			"CREATE TABLE " + TABLE_ALL_COURSES + " (" +
					COURSE_ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
					COURSE_NAME + " TEXT, " + 
					START_DATE + " TEXT, " + 
					END_DATE + " TEXT, " +
					CLASS_DAYS + " TEXT, " +
					START_TIME + " TEXT, " +
					END_TIME + " TEXT, " +
					TWSS + " TEXT, " +
					CLASS_LOCATION + " TEXT, " +
					INSTRUCTOR + " INTEGER, " +
					TA + " INTEGER " + 
					")";

	//table name, table columns and create table query for Instructors	
	public static final String TABLE_INSTRUCTOR = "instructor";
	public static final String INSTRUCTOR_ID = "instructor_id";
	public static final String INSTRUCTOR_TYPE = "instructor_type";
	public static final String INSTRUCTOR_NAME = "instructor_name";
	public static final String PHONE_NO = "phone_no";
	public static final String EMAIL_ADD = "email_add";
	public static final String OFFICE_LOCATION = "office_location";

	public static final String CREATE_TABLE_INSTRUCTOR = 
			"CREATE TABLE " + TABLE_INSTRUCTOR + " (" +
					INSTRUCTOR_ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
					INSTRUCTOR_TYPE + " TEXT, " + 
					INSTRUCTOR_NAME + " TEXT, " + 
					PHONE_NO + " TEXT, " +
					EMAIL_ADD + " TEXT, " +
					OFFICE_LOCATION + " TEXT " + 
					")";	

	//table name, table columns and create table query for Activity	
	public static final String TABLE_ACTIVITIES = "activities";
	public static final String ACTIVITY_ID = "activity_id";
	public static final String ACTIVITY_TYPE = "activity_type";
	public static final String ACTIVITY_NAME = "activity_name";
	public static final String DUE_DATE = "due_date";
	public static final String DESCRIPTION = "description";
	public static final String IS_COMPLETED = "is_completed";
	public static final String MAX_GRADE = "max_grade";
	public static final String OBT_GRADE = "obt_grade";

	public static final String CREATE_TABLE_ACTIVITIES = 
			"CREATE TABLE " + TABLE_ACTIVITIES + " (" +
					ACTIVITY_ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
					ACTIVITY_TYPE + " TEXT, " + 
					ACTIVITY_NAME + " TEXT, " + 
					DUE_DATE + " TEXT, " +
					DESCRIPTION + " TEXT, " +
					IS_COMPLETED + " TEXT, " + 
					MAX_GRADE + " TEXT, " + 
					OBT_GRADE + " TEXT, " +
					COURSE_ID + " INTEGER " +
					")";


	//table name, table columns and create table query for Study Sessions	
	public static final String TABLE_STUDY_SESSIONS = "study_sessions";
	public static final String STUDY_SESSION_ID = "study_session_id";
	public static final String DATE = "date";
	public static final String COUNT = "count";


	public static final String CREATE_TABLE_STUDY_SESSIONS = 
			"CREATE TABLE " + TABLE_STUDY_SESSIONS + " (" +
					STUDY_SESSION_ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
					DATE + " TEXT " +
					COUNT + " INTEGER, " + 
					COURSE_ID + " INTEGER " +
					")";

	public KottDBOpenHelper(Context context) {
		super(context, DATABASE_NAME, null, DATABASE_VERSION, null);
	}

	@Override
	public void onCreate(SQLiteDatabase db) {
		Log.i(LOGTAG, CREATE_TABLE_INSTRUCTOR);
		db.execSQL(CREATE_TABLE_INSTRUCTOR);
		Log.i(LOGTAG, TABLE_INSTRUCTOR + " table created");		

		Log.i(LOGTAG, CREATE_TABLE_ALL_COURSES);
		db.execSQL(CREATE_TABLE_ALL_COURSES);
		Log.i(LOGTAG, TABLE_ALL_COURSES + " table created");

		Log.i(LOGTAG, CREATE_TABLE_ACTIVITIES);
		db.execSQL(CREATE_TABLE_ACTIVITIES);
		Log.i(LOGTAG, TABLE_ACTIVITIES + " table created");

		Log.i(LOGTAG, CREATE_TABLE_STUDY_SESSIONS);
		db.execSQL(CREATE_TABLE_STUDY_SESSIONS);
		Log.i(LOGTAG, TABLE_STUDY_SESSIONS + " table created");
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		db.execSQL("DROP TABLE IF EXISTS " + TABLE_INSTRUCTOR);
		db.execSQL("DROP TABLE IF EXISTS " + TABLE_ALL_COURSES);
		db.execSQL("DROP TABLE IF EXISTS " + TABLE_ACTIVITIES);
		db.execSQL("DROP TABLE IF EXISTS " + TABLE_STUDY_SESSIONS);
		onCreate(db);
	}
}
